import pymysql
from pymysql.cursors import DictCursor
import logging
import datetime
import decimal
from typing import Any, Dict, List, Optional, Tuple, Union
from app.conf.db_config import DB_CONFIG
from app.conf.config import logger, config

# 使用简单连接方式替代连接池，确保兼容性


class MySQLHelper:
    """
    MySQL 数据库通用操作工具类 - 增强版
    支持：
        - 从集中配置获取连接信息
        - 连接池管理（使用pymysql连接池）
        - 两种操作模式（SQL模式 / 自动拼接模式）
        - 增删改查、批量插入、分页、事务
        - 可自动将 datetime/decimal/bytes 转换为 JSON 友好格式
        - 增强日志记录和错误处理
    """
    
    # 类级别的连接池，确保全局只有一个连接池实例
    _connection_pool = None
    
    def __init__(self, auto_commit=False, database=None, convert_datetime=True, datetime_format="%Y-%m-%d %H:%M:%S"):
        """
        初始化数据库工具类
        保持与mysql_helper.py的兼容性，主要参数为auto_commit

        Args:
            auto_commit (bool): 是否自动提交事务
            database (str): 要连接的数据库名称（保留以兼容旧代码）
            convert_datetime (bool): 是否自动转换 datetime/decimal 为字符串
            datetime_format (str): datetime 转换格式
        """
        # 忽略database参数，统一使用配置中的数据库名
        self.database = config.DB_NAME
        self.config = DB_CONFIG
        self.auto_commit = auto_commit
        self.convert_datetime = convert_datetime
        self.datetime_format = datetime_format
        self.conn = None
        self._ensure_pool()

    def _ensure_pool(self):
        """确保连接可用（简化版本，直接创建连接）"""
        # 移除连接池实现，直接在需要时创建连接
        logger.info("数据库连接初始化完成")
    
    def _get_connection(self):
        """获取数据库连接"""
        try:
            # 直接创建连接
            if not self.conn or not self.conn.open:
                self.conn = pymysql.connect(
                    host=self.config['host'],
                    port=self.config['port'],
                    user=self.config['user'],
                    password=self.config['password'],
                    database=self.database,
                    charset=self.config.get('charset', 'utf8mb4'),
                    cursorclass=DictCursor,
                    autocommit=True,
                    connect_timeout=self.config.get('timeout', 10)
                )
                logger.info(f"成功连接到数据库: {self.database}")
            return self.conn
        except Exception as e:
            logger.error(f"获取数据库连接失败: {str(e)}")
            raise

    def _close_connection(self):
        """关闭数据库连接"""
        if self.conn and self.conn.open:
            self.conn.close()
            self.conn = None
    
    def transaction(self):
        """事务上下文管理器"""
        # 导入mysql_helper中的TransactionContext以保持一致性
        from app.utils.mysql_helper import TransactionContext
        return TransactionContext(self)

    # ---------------------------
    # 工具函数 - 序列化结果
    # ---------------------------
    def _serialize_value(self, value: Any) -> Any:
        """将单个值序列化为可 JSON 化的类型"""
        if value is None:
            return None
        if isinstance(value, datetime.datetime):
            return value.strftime(self.datetime_format)
        if isinstance(value, datetime.date):
            return value.strftime("%Y-%m-%d")
        if isinstance(value, datetime.time):
            return value.strftime("%H:%M:%S")
        if isinstance(value, decimal.Decimal):
            return str(value)
        if isinstance(value, (bytes, bytearray)):
            try:
                return value.decode("utf-8")
            except Exception:
                return value.hex()
        return value

    def _serialize_row(self, row: Any) -> Any:
        """递归序列化查询结果"""
        if row is None:
            return None
        if isinstance(row, dict):
            return {k: self._serialize_row(v) for k, v in row.items()}
        if isinstance(row, (list, tuple)):
            return [self._serialize_row(v) for v in row]
        return self._serialize_value(row)

    # ---------------------------
    # 通用 SQL 执行
    # ---------------------------
    def execute_sql(
        self,
        sql: str,
        params: Optional[Union[Tuple, List, Dict]] = None,
        fetchone: bool = False,
        fetchall: bool = False,
    ) -> Union[int, Dict, List[Dict], None]:
        """执行SQL语句 - 增强版
        支持参数化查询，具有完善的日志记录和错误处理
        
        Args:
            sql: SQL语句
            params: SQL参数（元组/列表/字典形式）
            fetchone: 是否只返回一条记录
            fetchall: 是否返回所有记录
            
        Returns:
            int: 修改语句影响的行数
            dict: fetchone=True时返回单条记录
            list[dict]: fetchall=True时返回所有记录
            None: 执行出错时返回None
        """
        cursor = None
        result = None
        connection = None
        
        try:
            connection = self._get_connection()
            cursor = connection.cursor()
            
            # 记录SQL执行日志（不记录敏感参数值）
            if params:
                param_count = len(params) if isinstance(params, (tuple, list)) else len(params)
                logger.debug(f"执行SQL: {sql}，参数数量: {param_count}")
            else:
                logger.debug(f"执行SQL: {sql}")
            
            # 执行SQL
            if params:
                affected_rows = cursor.execute(sql, params)
            else:
                affected_rows = cursor.execute(sql)
            
            # 获取结果
            if fetchone:
                result = cursor.fetchone()
                if result:
                    result = self._serialize_row(result)
            elif fetchall:
                result = cursor.fetchall()
                if result:
                    result = self._serialize_row(result)
            else:
                result = affected_rows
                
        except Exception as e:
            # 详细记录错误
            logger.error(f"执行SQL失败: {str(e)}")
            logger.error(f"SQL语句: {sql}")
            # 如果发生错误且不在事务中，确保连接关闭
            if connection and self.auto_commit:
                try:
                    connection.close()
                    self.conn = None
                except:
                    pass
            result = None
        
        finally:
            # 清理资源
            if cursor and not cursor.closed:
                cursor.close()
            
            # 在自动提交模式下关闭连接
            if self.auto_commit and connection and connection.open:
                try:
                    connection.close()
                    self.conn = None
                except:
                    pass
                    
        return result

    # ---------------------------
    # 自动 SQL 拼接模式
    # ---------------------------
    def select(
        self,
        table: str,
        columns: Optional[List[str]] = None,
        where: Optional[Dict[str, Any]] = None,
        limit: Optional[int] = None,
        order_by: Optional[str] = None,
    ) -> List[Dict[str, Any]]:
        """
        执行 SELECT 查询

        Args:
            table (str): 表名
            columns (list): 要查询的列名
            where (dict): 查询条件
            limit (int): 限制返回条数
            order_by (str): 排序字段

        Returns:
            list[dict]: 查询结果
        """
        cols = ", ".join(columns) if columns else "*"
        sql = f"SELECT {cols} FROM `{table}`"
        params = []
        if where:
            conditions = " AND ".join([f"`{k}`=%s" for k in where])
            sql += f" WHERE {conditions}"
            params = list(where.values())
        if order_by:
            sql += f" ORDER BY {order_by}"
        if limit:
            sql += f" LIMIT {limit}"

        return self.execute_sql(sql, params, fetchall=True)

    def insert(self, table: str, data: Dict[str, Any]) -> int:
        """
        执行 INSERT 操作
        """
        keys = ", ".join([f"`{k}`" for k in data])
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO `{table}` ({keys}) VALUES ({placeholders})"
        return self.execute_sql(sql, list(data.values()))

    def update(self, table: str, data: Dict[str, Any], where: Dict[str, Any]) -> int:
        """
        执行 UPDATE 操作
        """
        set_clause = ", ".join([f"`{k}`=%s" for k in data])
        where_clause = " AND ".join([f"`{k}`=%s" for k in where])
        sql = f"UPDATE `{table}` SET {set_clause} WHERE {where_clause}"
        params = list(data.values()) + list(where.values())
        return self.execute_sql(sql, params)

    def delete(self, table: str, where: Dict[str, Any]) -> int:
        """
        执行 DELETE 操作
        """
        where_clause = " AND ".join([f"`{k}`=%s" for k in where])
        sql = f"DELETE FROM `{table}` WHERE {where_clause}"
        return self.execute_sql(sql, list(where.values()))

    def batch_insert(self, table: str, data_list: List[Dict[str, Any]]) -> int:
        """
        批量插入多条记录
        """
        if not data_list:
            return 0
        keys = ", ".join([f"`{k}`" for k in data_list[0].keys()])
        placeholders = ", ".join(["%s"] * len(data_list[0]))
        sql = f"INSERT INTO `{table}` ({keys}) VALUES ({placeholders})"
        conn = self._get_connection()
        with conn.cursor() as cursor:
            try:
                cursor.executemany(sql, [tuple(d.values()) for d in data_list])
                if self.auto_commit:
                    conn.commit()
                return cursor.rowcount
            except Exception as e:
                conn.rollback()
                logging.error(f"批量插入失败: {e}")
                raise
            finally:
                self._close_connection()

    def paginate_select(
        self,
        table: str,
        columns: Optional[List[str]] = None,
        where: Optional[Dict[str, Any]] = None,
        page: int = 1,
        page_size: int = 10,
        order_by: Optional[str] = None,
    ) -> Dict[str, Any]:
        """
        分页查询

        Returns:
            dict:
                {
                    "page": 当前页,
                    "page_size": 每页数量,
                    "total": 总数,
                    "data": 当前页数据
                }
        """
        offset = (page - 1) * page_size
        cols = ", ".join(columns) if columns else "*"

        # 查询数据
        sql = f"SELECT {cols} FROM `{table}`"
        params = []
        if where:
            conditions = " AND ".join([f"`{k}`=%s" for k in where])
            sql += f" WHERE {conditions}"
            params = list(where.values())
        if order_by:
            sql += f" ORDER BY {order_by}"
        sql += f" LIMIT {page_size} OFFSET {offset}"
        data = self.execute_sql(sql, params, fetchall=True)

        # 查询总数
        count_sql = f"SELECT COUNT(*) AS total FROM `{table}`"
        if where:
            count_sql += " WHERE " + " AND ".join([f"`{k}`=%s" for k in where])
        total_result = self.execute_sql(count_sql, params, fetchone=True)
        total = total_result.get("total", 0) if total_result else 0

        return {"page": page, "page_size": page_size, "total": total, "data": data}

    def execute_transaction(self, sql_list: List[Tuple[str, Tuple[Any, ...]]]) -> bool:
        """执行事务 - 增强版
        提供详细的错误记录和回滚机制
        
        Args:
            sql_list: SQL语句和参数列表，格式为 [(sql, params), (sql, params), ...]
            
        Returns:
            bool: 成功返回True，失败返回False
        """
        cursor = None
        connection = None
        success = False
        
        try:
            # 获取连接并禁用自动提交
            connection = self._get_connection()
            original_autocommit = connection.autocommit
            connection.autocommit = False
            cursor = connection.cursor()
            
            logger.debug(f"开始执行事务，共 {len(sql_list)} 条SQL语句")
            
            # 执行所有SQL语句
            for i, (sql, params) in enumerate(sql_list, 1):
                try:
                    logger.debug(f"执行事务中的SQL #{i}: {sql}")
                    if params:
                        cursor.execute(sql, params)
                    else:
                        cursor.execute(sql)
                except Exception as e:
                    logger.error(f"事务中的SQL #{i} 执行失败: {str(e)}")
                    logger.error(f"SQL语句: {sql}")
                    raise
            
            # 提交事务
            connection.commit()
            success = True
            logger.debug("事务提交成功")
            
        except Exception as e:
            # 发生错误时回滚
            if connection:
                try:
                    connection.rollback()
                    logger.debug("事务回滚成功")
                except Exception as rollback_error:
                    logger.error(f"事务回滚失败: {str(rollback_error)}")
            logger.error(f"事务执行失败: {str(e)}")
            
        finally:
            # 清理资源
            if cursor and not cursor.closed:
                cursor.close()
                
            # 恢复自动提交设置并关闭连接
            if connection:
                try:
                    connection.autocommit = original_autocommit
                    if connection.open:
                        connection.close()
                        self.conn = None
                except Exception as close_error:
                    logger.error(f"关闭事务连接失败: {str(close_error)}")
                    
        return success

    # ---------------------------
    # 上下文管理
    # ---------------------------
    def __enter__(self):
        self._get_connection()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.conn.rollback()
        elif self.auto_commit:
            self.conn.commit()
        self._close_connection()
